<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017\11\15 0015
 * Time: 16:07
 */

namespace backend\models;

use PHPExcel;
use PHPExcel_Reader_Excel2007;
use PHPExcel_Reader_Excel5;
use PHPExcel_IOFactory;

class Excel
{

    /**
     * 读取excel表格中的数据
     * @author xxx
     * @dateTime 2017-06-12T09:39:01+0800
     * @param    string $filePath excel文件路径
     * @param    integer $startRow 开始的行数
     * @return   array
     */
    public function getExcelData($filePath, $startRow = 1)
    {
        $PHPExcel = new PHPExcel();
        $PHPReader = new PHPExcel_Reader_Excel2007();
        //默认用excel2007读取excel，若格式不对，则用之前的版本进行读取
        //setReadDataOnly Set read data only 只读单元格的数据，不格式化 e.g. 读时间会变成一个数据等
        $PHPReader->setReadDataOnly(TRUE);
        if (!$PHPReader->canRead($filePath)) {
            $PHPReader = new PHPExcel_Reader_Excel5();
            //setReadDataOnly Set read data only 只读单元格的数据，不格式化 e.g. 读时间会变成一个数据等
            $PHPReader->setReadDataOnly(TRUE);
            if (!$PHPReader->canRead($filePath)) {
                //echo 'can not read excel';
                //return;
                throw new \PHPExcel_Reader_Exception("Could not open " . $filePath . " for reading!");
            }
        }

        /*$inputFileType = PHPExcel_IOFactory::identify($filePath);
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $PHPExcel = $objReader->load($filePath);*/

        $PHPExcel = $PHPReader->load($filePath);
        //获取sheet的数量
        $sheetCount = $PHPExcel->getSheetCount();
        //获取sheet的名称
        $sheetNames = $PHPExcel->getSheetNames();

        //获取所有的sheet表格数据
        $excleDatas = array();
        $emptyRowNum = 0;
        for ($i = 0; $i < $sheetCount; $i++) {
            /**读取excel文件中的第一个工作表*/
            $currentSheet = $PHPExcel->getSheet($i);
            /**取得最大的列号*/
            $allColumn = $currentSheet->getHighestColumn();
            /**取得一共有多少行*/
            $allRow = $currentSheet->getHighestRow();

            $arr = array();
            for ($currentRow = $startRow; $currentRow <= $allRow; $currentRow++) {
                /**从第A列开始输出*/
                for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65, $currentRow)->getValue();
                    $arr[$currentRow][] = trim($val);
                }

                //单元格为空不过滤
                //$arr[$currentRow] = array_filter($arr[$currentRow]);

                //统计连续空行
                if (empty($arr[$currentRow]) && $emptyRowNum <= 50) {
                    $emptyRowNum++;
                } else {
                    $emptyRowNum = 0;
                }
                //防止坑队友的同事在excel里面弄出很多的空行，陷入很漫长的循环中，设置如果连续超过50个空行就退出循环，返回结果
                //连续50行数据为空，不再读取后面行的数据，防止读满内存
                if ($emptyRowNum > 50) {
                    break;
                }
            }
            $excleDatas[$i] = $arr; //多个sheet的数组的集合
        }

        //这里我只需要用到第一个sheet的数据，所以只返回了第一个sheet的数据
        $returnData = $excleDatas ? array_shift($excleDatas) : [];

        //第一行数据就是空的，为了保留其原始数据，第一行数据就不做array_fiter操作；
        $returnData = $returnData && isset($returnData[$startRow]) && !empty($returnData[$startRow]) ? array_filter($returnData) : $returnData;
        return $returnData;
        //return $excleDatas  ? array_filter(array_shift($excleDatas)) : [];
    }

    /**
     * 生成excel数据表
     * e.g.
     * $fields = [
     *     ['key' => 'province', 'name' => '省', 'required' => false]
     *     ['key' => 'city', 'name' => '市', 'required' => true]
     *     ['key' => 'district', 'name' => '区/县', 'required' => true]
     *     ['key' => 'street', 'name' => '街道', 'required' => true]
     * ];
     *
     * $dataList = [
     *     ['province' => 'xx省' , 'city'=>'xx市' , 'district' => 'xx县' ,'street' => 'xx街道'],
     *     ['province' => 'xx省' , 'city'=>'xx市' , 'district' => 'xx县' ,'street' => 'xx街道'],
     *     ['province' => 'xx省' , 'city'=>'xx市' , 'district' => 'xx县' ,'street' => 'xx街道'],
     *     ['province' => 'xx省' , 'city'=>'xx市' , 'district' => 'xx县' ,'street' => 'xx街道'],
     * ]
     *
     * @author xxx
     * @dateTime 2017-07-18T15:19:04+0800
     * @param    array $fileds 表头数据
     * @param    array $dataList 导出数据的数组
     * @param    string $fileName 生成的文件名
     * @return   mix
     */
    public function createExcelFromData($fileds, $dataList = array(), $fileName = 'data')
    {
        if (!count($fileds || !count($dataList))) {
            return false;
        }
        $dataList = array_values($dataList);
        $fieldConfig = array();
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        $i = 0;
        foreach ($fileds as $key => $value) {
            $cloumStr = chr(ord("A") + $key);
            $column = $cloumStr . "1";
            //必填字段列表标红
            $required = isset($value['required']) ? $value['required'] : false;
            if ($required == true) {
                //把必填字段标红
                $objPHPExcel->getActiveSheet()->getStyle($column)->getFont()->getColor()->setARGB('FF0000');
            }
            $objPHPExcel->getActiveSheet()->setCellValue($column, $value['name']);
            $i++;
        }

        $filedKeys = array_column($fileds, 'key');
        $list = [];
        ob_start();
        foreach ($dataList as $key => $value) {
            if ($key % 5000 == 0) {
                ob_flush();
                flush();
            }
            $num = $key + 2;
            for ($j = 0; $j < $i; $j++) {
                $cloumStr = chr(ord("A") + $j);
                $column = $cloumStr . $num;
                if (isset($value[$filedKeys[$j]])) {
                    $objPHPExcel->getActiveSheet()->setCellValue($column, $value[$filedKeys[$j]]);
                } else {
                    $objPHPExcel->getActiveSheet()->setCellValue($column, '');
                }
            }
        }

        //设置必填字段字体颜色
        $objPHPExcel->getActiveSheet()->setTitle('Simple');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        if (php_sapi_name() != 'cli') {
            $fileName = basename($fileName);
            $fileName = iconv("utf-8", "gb2312", $fileName);
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename=' . $fileName);
            header('Cache-Control: max-age=0');
            $objWriter->save('php://output'); //文件通过浏览器下载
        } else {

            $dirname = dirname($fileName);
            if ($dirname != '.') {
                //文件路径如果不存在则递归创健
                CommonFun::recursionMkDir($dirname);
            }
            $objWriter->save($fileName); //脚本方式运行，保存在指定目录
            if (!file_exists($fileName)) {
                return false;
            }
            return true;
        }
    }
    /*生成项目列表xls 表 下载
   *（ly）
     * $type:1 混合 2 抵押 3 两个表
   */
    public function createExcelProjects($type, $dataListHH = array(),$dataListDY = array(), $fileName = 'data.xlsx')
    {
        if (!$type || (!count($dataListHH) && !count($dataListDY))) {
            return false;
        }
//        $dataList = array_values($dataList);
//        $fieldConfig = array();
        $i=0;
        $objPHPExcel = new PHPExcel();
        if($type <> 2){//混合类
            $objPHPExcel =$this->createExcelProjectsHH($objPHPExcel,$dataListHH,$i);
            $i++;
        }
        if($type <> 1){//抵押类
            $objPHPExcel=$this->createExcelProjectsDY($objPHPExcel,$dataListDY,$i);
        }
        //设置必填字段字体颜色
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        //if (php_sapi_name() != 'cli') {
        $fileName = basename($fileName);
        $fileName = iconv("utf-8", "gb2312", $fileName);
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename=' . $fileName);
        // header('Cache-Control: max-age=0');
        $objWriter->save('php://output');exit; //文件通过浏览器下载
//        } else {
//            $dirname = dirname($fileName);
//            if ($dirname != '.') {
//                //文件路径如果不存在则递归创健
//                CommonFun::recursionMkDir($dirname);
//            }
//            $objWriter->save($fileName); //脚本方式运行，保存在指定目录
//            if (!file_exists($fileName)) {
//                return false;
//            }
//            return true;
//        }
    }

    /*混合类项目列表（ly）*/
    public function createExcelProjectsHH($_objPHPExcel,$_data,$_i){
        /*混合类--标题*/
        $styleThinBlackBorderOutline = array(
            'borders' => array (
                'allborders' => array (
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,   //设置border样式
                    //'style' => PHPExcel_Style_Border::BORDER_THICK,  另一种样式
                    'color' => array ('argb' => 'FF000000'),          //设置border颜色
                ),
            ),
        );
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'A2:AA2')->applyFromArray($styleThinBlackBorderOutline);//添加边框
        $_objPHPExcel->setActiveSheetIndex($_i);
        $_objPHPExcel->getActiveSheet($_i)->getStyle('A1:AA1')->getFont()->setSize(16)->setBold(true);
        $_objPHPExcel->getActiveSheet($_i)->getStyle('A1:AA1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $_objPHPExcel->getActiveSheet($_i)->mergeCells('A1:M1');
        $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('A1', '基本信息');
//        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'N1:Q1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $_objPHPExcel->getActiveSheet($_i)->mergeCells('N1:Q1');
//        $_objPHPExcel->getActiveSheet($_i)->getStyle('N1')->getFill()->getStartColor()->setARGB('FFFFFF00');
        $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('N1', '涉诉信息');
        $_objPHPExcel->getActiveSheet($_i)->mergeCells('R1:U1');
        $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('R1', '价值评估');
        $_objPHPExcel->getActiveSheet($_i)->mergeCells('V1:AA1');
        $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('V1', '补充说明');
        $_objPHPExcel->getActiveSheet($_i)->setTitle('项目（保证类、重组类、物权类、混合类）');
        /*混合类二级标题 第二行*/
        $fileds=['序号','是否亮点','借款人名称','省份地区','本金（元）','利息（元）','本息合计（元）','保证人/抵押人','借款人状况','保证人/抵押人状况','抵押物类型','财产或亮点明细', '诉讼状态',
                '判决','执行','法院','列失信',
                '参考资产市场价值','	回现保守','回现激进','回收预评估价值说明',
                '法律依据','处置方案','风险点','承债人现状','其他问题','需再补充'];
       //标题
        foreach ($fileds as $key => $value) {
            /*计算行对象的字段*/
            $addKey=$key%26;
            $jWKey=floor($key/26);
            $tKey='';
            if($jWKey>0){
                $jWKey--;
                $tKey=chr(ord("A") + $jWKey);
            }
            $cloumStr = $tKey.chr(ord("A") + $addKey);
            $column = $cloumStr . "2";
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue($column, $value);
        }
        /*基本信息 添加背景色*/
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'A2:M2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'A2:M2')->getFill()->getStartColor()->setARGB('fcff36');
        /*涉诉信息 添加背景色*/
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'N2:Q2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'N2:Q2')->getFill()->getStartColor()->setARGB('fdc026');
        /*价值评估 添加背景色*/
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'R2:U2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'R2:U2')->getFill()->getStartColor()->setARGB('e4b6b6');
        /*补充说明 添加背景色*/
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'V2:AA2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'V2:AA2')->getFill()->getStartColor()->setARGB('24b0ee');
//        $_objPHPExcel->getActiveSheet($_i)->getStyle('A2:AA2')->getFont()->setBold(true);
        /*写入数据 $_data*/
//        $filedKeys = array_column($fileds, 'key');
//        $list = [];
        $j=1;
        ob_start();
        $managementStatus=\Yii::$app->params['managementStatus'];
//        $mortgageType=\Yii::$app->params['mortgageType'];
        $isSuit=\Yii::$app->params['isSuit'];
        $mortgageType = \Yii::$app->params['mortgageTypes3'];
        foreach ($_data as $key => $value) {
            if ($key % 5000 == 0) {
                ob_flush();
                flush();
            }
            $column=$j+2;
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('A'.$column, $j);

            //序号	is_highlights	borrower	provinceName['title']-cityName['title']	principal	interest	（principal+interest）	guarantor	management_status	guarantor_status	mortgage_type	highlights	is_suit
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('B'.$column, $value['is_highlights']==1?'是':'否');
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('C'.$column, $value['borrower']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('D'.$column, $value['provinceName']['title'].$value['cityName']['title']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('E'.$column, $value['principal']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('F'.$column, $value['interest']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('G'.$column,($value['principal']+$value['interest']));
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('H'.$column, $value['guarantor']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('I'.$column,$managementStatus[$value['management_status']]);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('J'.$column,$value['guarantor_status']);

            $mortgageTypeStr='';//抵押物类型名称
            $mortgage_type=explode(',',$value['mortgage_type']);
            foreach($mortgage_type as $val){
                if($val>0){
                    if($mortgageTypeStr) $mortgageTypeStr.=',';
                    $mortgageTypeStr.=$mortgageType[$val]['name'];
                }
            }
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('K'.$column,$mortgageTypeStr);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('L'.$column, $value['highlights']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('M'.$column, $isSuit[$value['is_suit']]['name']);
            //lawsuit.judgement	lawsuit.execution	lawsuit.court	lawsuit.block
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('N'.$column, $value['lawsuit']['judgement']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('O'.$column, $value['lawsuit']['execution']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('P'.$column, $value['lawsuit']['court']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('Q'.$column, $value['lawsuit']['block']);
            //evaluation.reference	evaluation.conservation	evaluation.radical	evaluation.recall
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('R'.$column, $value['evaluation']['reference']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('S'.$column, $value['evaluation']['conservation']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('T'.$column, $value['evaluation']['radical']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('U'.$column, $value['evaluation']['recall']);
            //note.legal  note.sell_plan	note.risk_point	note.current_state	note.other_problem	note.more_problem
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('V'.$column, $value['note']['legal']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('W'.$column, $value['note']['sell_plan']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('X'.$column, $value['note']['risk_point']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('Y'.$column, $value['note']['current_state']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('Z'.$column, $value['note']['other_problem']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AA'.$column, $value['note']['more_problem']);
            $j++;
        }
        return $_objPHPExcel;
    }
    /*抵押类项目列表（ly）*/
    public function createExcelProjectsDY($_objPHPExcel,$_data,$_i){
        /*抵押类*/
        if($_i==1){//若生成第二个sheet里面
            $_objPHPExcel->createSheet();
        }
        $_objPHPExcel->setActiveSheetIndex($_i);
        $styleThinBlackBorderOutline = array(
            'borders' => array (
                'allborders' => array (
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,   //设置border样式
                    //'style' => PHPExcel_Style_Border::BORDER_THICK,  另一种样式
                    'color' => array ('argb' => 'FF000000'),          //设置border颜色
                ),
            ),
        );
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'A2:BV2')->applyFromArray($styleThinBlackBorderOutline);//添加边框
        $_objPHPExcel->getActiveSheet($_i)->getStyle('A1:BV1')->getFont()->setSize(16)->setBold(true);
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'A1:BV1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

//        $_objPHPExcel->getActiveSheet($_i)->mergeCells('C1:M1');
//        $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('C1','XXX包预评估表');
//        $_objPHPExcel->getActiveSheet($_i)->getStyle('C1')->getFont()->getColor()->setARGB('FF0000');

        $_objPHPExcel->getActiveSheet($_i)->mergeCells('A1:K1');
        $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('A1','基本信息');

        $_objPHPExcel->getActiveSheet($_i)->mergeCells('L1:O1');
        $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('L1','司法进展');

        $_objPHPExcel->getActiveSheet($_i)->mergeCells('P1:Z1');
        $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('P1','抵押物信息');
        $_objPHPExcel->getActiveSheet($_i)->getStyle('P1')->getFont()->getColor()->setARGB('FF0000');

        $_objPHPExcel->getActiveSheet($_i)->mergeCells('AA1:AD1');
        $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AA1','抵押情况');

        $_objPHPExcel->getActiveSheet($_i)->mergeCells('AE1:AG1');
        $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AE1','查封情况');

        $_objPHPExcel->getActiveSheet($_i)->mergeCells('AH1:BA1');
        $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AH1','现场访谈');

        $_objPHPExcel->getActiveSheet($_i)->mergeCells('BB1:BL1');
        $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BB1','定价与评估');

        $_objPHPExcel->getActiveSheet($_i)->mergeCells('BM1:BQ1');
        $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BM1','非抵押物情况');

        $_objPHPExcel->getActiveSheet($_i)->mergeCells('BR1:BV1');
        $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BR1','回现评估');
        $_objPHPExcel->getActiveSheet($_i)->setTitle('项目（抵押类）');
        /*抵押类 三级标题*/
        $fileds=['序号','是否亮点','借款人名称','省份地区','本金（元）','利息（元）','本息合计（元）','诉讼状态','借款人状况','保证人/抵押人','保证人/抵押人状况',
                '判决','执行','法院','列失信',
                '抵押本金','抵押利息','权属人','抵押物类型','抵押物编号/他项权证号','所在区域','抵押物地址','建筑面积（㎡）','土地面积（㎡）','	最高抵押额度（元）','剩余使用年限',
                '抵押物抵押顺序','是否首押','后续抵押情况','抵押情况描述',
                '抵押物查封顺序','是否首封','查封情况描述',
                '楼盘名称','建成日期','总层数','所在层数','区位描述/周边氛围','物业通达性','物业维护状况','目前状态','租赁/租赁时间/租金缴纳','是否业主唯一住房','业主还款意愿','市场走访价格（售价/套）','市场走访租金','可比物业1','可比物业2','可比物业3','可比物业4',	'走访信息来源','联系方式','备注说明',
                '银行评估价值-总价（元）','第三方评估价-总价（元）','第三方评估价-清算价值（元）','司法拍卖单价（元/㎡）','市场保守单价（元/㎡）','市场激进单价（元/㎡）','市场保守总价（元）','市场激进总价（元）','抵押物变现价保守（元）','抵押物变现价激进（元）','抵押物定价依据',
                '查封情况','查封顺序','回收（保守）','回收（乐观）','回收亮点分析',
                '回现保守','回现激进','过户税费','回现周期（月）','回现分析'];
        //标题
        foreach ($fileds as $key => $value) {
            /*计算行对象的字段*/
            $addKey=$key%26;
            $jWKey=floor($key/26);
            $tKey='';
            if($jWKey>0){
                $jWKey--;
                $tKey=chr(ord("A") + $jWKey);
            }
            $cloumStr = $tKey.chr(ord("A") + $addKey);
            $column = $cloumStr . "2";
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue($column, $value);
        }
        /*基本信息 添加背景色*/
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'A2:K2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'A2:K2')->getFill()->getStartColor()->setARGB('fcff36');
        /*司法进展 添加背景色*/
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'L2:O2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'L2:O2')->getFill()->getStartColor()->setARGB('fdc026');
        /*抵押物信息 抵押情况  查封情况 添加背景色*/
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'P2:AG2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'P2:AG2')->getFill()->getStartColor()->setARGB('e5b7b7');
        /*现场访谈 添加背景色*/
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'AH2:BA2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'AH2:BA2')->getFill()->getStartColor()->setARGB('b9cce3');
        /*定价与评估 添加背景色*/
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'BB2:BL2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'BB2:BL2')->getFill()->getStartColor()->setARGB('b29fc6');
        /*非抵押物情况 添加背景色*/
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'BM2:BQ2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'BM2:BQ2')->getFill()->getStartColor()->setARGB('c6d9f0');
        /*回现评估 添加背景色*/
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'BR2:BV2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $_objPHPExcel->getActiveSheet($_i)->getStyle( 'BR2:BV2')->getFill()->getStartColor()->setARGB('fcff36');
       // $_objPHPExcel->getActiveSheet($_i)->getStyle('A3:'.$column)->getFont()->setBold(true);//三级标题加粗
        /*写入数据 $_data*/

        $j=1;
        $managementStatus=\Yii::$app->params['managementStatus'];
        $isSuit=\Yii::$app->params['isSuit'];
        $mortgageTypes3=\Yii::$app->params['mortgageTypes3'];//抵押物类型（抵押表）
        ob_start();
        foreach ($_data as $key => $value) {
            if ($key % 5000 == 0) {
                ob_flush();
                flush();
            }
            $column = $j + 2;
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('A' . $column, $j);
            //序号	is_highlights	borrower	pctitle+pcctitle	principal	interest	（principal+interest）	is_suit	management_status	 guarantor	 guarantor_status
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('B' . $column, $value['is_highlights'] == 1 ? '是' : '否');
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('C' . $column, $value['borrower']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('D' . $column, $value['pctitle'] . $value['pcctitle']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('E' . $column, $value['principal']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('F' . $column, $value['interest']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('G' . $column, ($value['principal'] + $value['interest']));
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('H' . $column, $isSuit[$value['is_suit']]['name']);//$value['is_suit']==1?'已诉':'未诉'
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('I' . $column, $managementStatus[$value['management_status']]);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('J' . $column, $value['guarantor']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('K' . $column, $value['guarantor_status']);
            //judgement	execution	court	block
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('L' . $column, $value['judgement']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('M' . $column, $value['execution']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('N' . $column, $value['court']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('O' . $column, $value['block']);
            //mortgage_principal	mortgage_interest	mortgage_owner	mortgage_type	mortgage_code	mortgage_district	mortgage_address	mortgage_building_area	mortgage_land_area	maximum_mortgage	remaining_service_life
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('P' . $column, $value['mortgage_principal']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('Q' . $column, $value['mortgage_interest']);
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('R' . $column, $value['mortgage_owner']);
            //获取抵押物类型名称-开始
            $mortgage_type_str='';//抵押物类型名称（抵押查封中）
            $mortgage_type=explode(',',$value['mortgage_type']);
            foreach ($mortgage_type as $val){
                if($val>0){//显示大于零的索引
                    if($mortgage_type_str) $mortgage_type_str.=',';
                    $mortgage_type_str.=$mortgageTypes3[$val]['name'];
                }
            }//获取抵押物类型名称-结束
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('S' . $column,$mortgage_type_str);//$value['mortgage_type']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('T' . $column, (isset($value['mortgage_code'])?$value['mortgage_code']:''));
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('U' . $column, (isset($value['mortgage_district'])?$value['mortgage_district']:''));//$value['mortgage_district']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('V' . $column, (isset($value['mortgage_address'])?$value['mortgage_address']:''));//$value['mortgage_address']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('W' . $column, (isset($value['mortgage_building_area'])?$value['mortgage_building_area']:''));//$value['mortgage_building_area']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('X' . $column, (isset($value['mortgage_land_area'])?$value['mortgage_land_area']:''));//$value['mortgage_land_area']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('Y' . $column, (isset($value['maximum_mortgage'])?$value['maximum_mortgage']:''));//$value['maximum_mortgage']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('Z' . $column, (isset($value['remaining_service_life'])?$value['remaining_service_life']:''));//$value['remaining_service_life']
            //mortgage_order	is_first_mortgage  	mortgage_future	mortgage_situation
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AA' . $column, (isset($value['mortgage_order'])?$value['mortgage_order']:''));//$value['mortgage_order']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AB' . $column, (isset($value['is_first_mortgage'])?$value['is_first_mortgage']:''));//$value['is_first_mortgage']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AC' . $column, (isset($value['mortgage_future'])?$value['mortgage_future']:''));//$value['mortgage_future']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AD' . $column, (isset($value['mortgage_situation'])?$value['mortgage_situation']:''));//$value['mortgage_situation']
            //mortgage_seal_order	is_first_seal	 mortgage_seal_state
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AE' . $column, (isset($value['mortgage_seal_order'])?$value['mortgage_seal_order']:''));//$value['mortgage_seal_order']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AF' . $column, (isset($value['is_first_seal'])?$value['is_first_seal']:''));//$value['is_first_seal']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AG' . $column, (isset($value['mortgage_seal_state'])?$value['mortgage_seal_state']:''));//$value['mortgage_seal_state']
            //building_title	finished_date	total_floor	which_floor	surroundings	property	property_state	whether_work（是否营业）	lease_info	is_only	willing	market_price	market_rent_price	comparison_property	 info_origin	contact	remark
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AH' . $column, (isset($value['building_title'])?$value['building_title']:''));//$value['building_title']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AI' . $column, (isset($value['finished_date'])?$value['finished_date']:''));//$value['finished_date']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AJ' . $column, (isset($value['total_floor'])?$value['total_floor']:''));//$value['total_floor']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AK' . $column, (isset($value['which_floor'])?$value['which_floor']:''));//$value['which_floor']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AL' . $column, (isset($value['surroundings'])?$value['surroundings']:''));//$value['surroundings']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AM' . $column, (isset($value['property'])?$value['property']:''));//$value['property']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AN' . $column, (isset($value['property_state'])?$value['property_state']:''));//$value['property_state']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AO' . $column, (isset($value['whether_work'])?$value['whether_work']:''));//$value['whether_work']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AP' . $column, (isset($value['lease_info'])?$value['lease_info']:''));//$value['lease_info']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AQ' . $column, (isset($value['is_only'])?$value['is_only']:''));//$value['is_only']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AR' . $column, (isset($value['willing'])?$value['willing']:''));//$value['willing']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AS' . $column, (isset($value['market_price'])?$value['market_price']:''));//$value['market_price']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AT' . $column, (isset($value['market_rent_price'])?$value['market_rent_price']:''));//$value['market_rent_price']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AU' . $column, (isset($value['comparison_property1'])?$value['comparison_property1']:''));//$value['comparison_property1']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AV' . $column, (isset($value['comparison_property2'])?$value['comparison_property2']:''));//$value['comparison_property2']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AW' . $column, (isset($value['comparison_property3'])?$value['comparison_property3']:''));//$value['comparison_property3']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AX' . $column, (isset($value['comparison_property4'])?$value['comparison_property4']:''));//$value['comparison_property4']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AY' . $column, (isset($value['info_origin'])?$value['info_origin']:''));//$value['info_origin']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('AZ' . $column, (isset($value['contact'])?$value['contact']:''));//$value['contact']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BA' . $column, (isset($value['remark'])?$value['remark']:''));//$value['remark']
            //bank_value 	third_partner_value	third_partner_value_other	auction_price	market_conserve_price	market_radical_price	market_conservation_total_price	market_radical_total_price	payment_reservation(1)	payment_radical(1)	price_reason
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BB' . $column, (isset($value['bank_value'])?$value['bank_value']:''));//$value['bank_value']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BC' . $column, (isset($value['third_partner_value'])?$value['third_partner_value']:''));//$value['third_partner_value']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BD' . $column, (isset($value['third_partner_value_other'])?$value['third_partner_value_other']:''));//$value['third_partner_value_other']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BE' . $column, (isset($value['auction_price'])?$value['auction_price']:''));//$value['auction_price']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BF' . $column, (isset($value['market_conserve_price'])?$value['market_conserve_price']:''));//$value['market_conserve_price']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BG' . $column, (isset($value['market_radical_price'])?$value['market_radical_price']:''));//$value['market_radical_price']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BH' . $column, (isset($value['market_conservation_total_price'])?$value['market_conservation_total_price']:''));//$value['market_conservation_total_price']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BI' . $column, (isset($value['market_radical_total_price'])?$value['market_radical_total_price']:''));//$value['market_radical_total_price']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BJ' . $column, (isset($value['vpayment_reservation'])?$value['vpayment_reservation']:''));//$value['vpayment_reservation']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BK' . $column, (isset($value['vpayment_radical'])?$value['vpayment_radical']:''));//$value['vpayment_radical']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BL' . $column, (isset($value['price_reason'])?$value['price_reason']:''));//$value['price_reason']
            //mortgage_other_seal_state	mortgage_other_seal_order	bought_reservation	bought_radical	bought_analysis
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BM' . $column, (isset($value['mortgage_other_seal_state'])?$value['mortgage_other_seal_state']:''));//$value['mortgage_other_seal_state']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BN' . $column, (isset($value['mortgage_other_seal_order'])?$value['mortgage_other_seal_order']:''));//$value['mortgage_other_seal_order']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BO' . $column, (isset($value['bought_reservation'])?$value['bought_reservation']:''));//$value['bought_reservation']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BP' . $column, (isset($value['bought_radical'])?$value['bought_radical']:''));//$value['bought_radical']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BQ' . $column, (isset($value['bought_analysis'])?$value['bought_analysis']:''));//$value['bought_analysis']
            //payment_reservation(2)	payment_radical(2) 	payment_tax	payment_month	payment_analysis
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BR' . $column, (isset($value['payment_reservation'])?$value['payment_reservation']:''));//$value['payment_reservation']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BS' . $column, (isset($value['payment_radical'])?$value['payment_radical']:''));//$value['payment_radical']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BT' . $column, (isset($value['payment_tax'])?$value['payment_tax']:''));//$value['payment_tax']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BU' . $column, (isset($value['payment_month'])?$value['payment_month']:''));//$value['payment_month']
            $_objPHPExcel->setActiveSheetIndex($_i)->setCellValue('BV' . $column, (isset($value['payment_analysis'])?$value['payment_analysis']:''));//$value['payment_analysis']
            $j++;
        }
        return $_objPHPExcel;
    }

}
